#激活虚拟环境.\venv\Scripts\activate.ps1
#激活虚拟环境.\venv\Scripts\activate.ps1
import matplotlib.pyplot as plt
from flask import Flask, render_template, request, redirect, url_for, jsonify, send_from_directory
from flask_sqlalchemy import SQLAlchemy
from datetime import datetime
import requests
import os
from werkzeug.utils import secure_filename  # 添加 secure_filename
from flask import send_file
from flask_migrate import Migrate
from openai import OpenAI
import shutil
import pytz
from dotenv import load_dotenv  # 新增导入
from test import EnhancedKnowledgeBase
import logging
from langchain.text_splitter import RecursiveCharacterTextSplitter
import glob


# 加载环境变量
load_dotenv()

# 在Flask应用初始化后添加配置
app = Flask(__name__)

# DeepSeek_V3 API配置（修改为从环境变量获取）
DEEPSEEK_API_KEY = os.getenv('DEEPSEEK_API_KEY')  # 必须配置
app.config['UPLOAD_FOLDER'] = os.path.join(os.getcwd(), 'uploads')  # 配置上传根目录
app.config['MAX_CONTENT_LENGTH'] = 16 * 1024 * 1024  # 限制上传文件为16MB
# app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///audit_system.db'
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///../instance/audit_system.db'
app.config['TIMEZONE'] = pytz.timezone('Asia/Shanghai')

# 新增图形文件配置 ▼▼▼▼
app.config['GRAPH_UPLOAD_FOLDER'] = os.path.join(app.config['UPLOAD_FOLDER'], 'graphs') 

db = SQLAlchemy(app)
migrate = Migrate(app, db)

# DeepSeek_V3 API配置
DEEPSEEK_API_URL = 'https://api.deepseek.com'

# 确保上传目录存在
os.makedirs(os.path.join(app.config['UPLOAD_FOLDER'], 'normative'), exist_ok=True)
os.makedirs(os.path.join(app.config['UPLOAD_FOLDER'], 'projects'), exist_ok=True)

# 在文件顶部定义允许的扩展名
ALLOWED_EXTENSIONS = {'.pdf', '.doc', '.docx', '.xls', '.xlsx', '.txt'}

# 数据库模型
class NormativeDocument(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(128), nullable=False)
    description = db.Column(db.Text)
    file_path = db.Column(db.String(256), nullable=False)
    uploaded_at = db.Column(db.DateTime, default=datetime.utcnow)
    category = db.Column(db.String(64))  # 文件分类
    version = db.Column(db.String(32))   # 文件版本
    # 显式指定外键约束名称
    project_id = db.Column(db.Integer, db.ForeignKey('project.id', name='fk_normative_document_project_id'))

class ProjectDocument(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    # 显式指定外键约束名称
    project_id = db.Column(db.Integer, db.ForeignKey('project.id', name='fk_project_document_project_id'), nullable=False)
    name = db.Column(db.String(128), nullable=False)
    description = db.Column(db.Text)
    file_path = db.Column(db.String(256), nullable=False)
    document_type = db.Column(db.String(64))  # 例如：合同、设计文件等
    uploaded_at = db.Column(db.DateTime, default=datetime.utcnow)

class InternalControlTest(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    # 显式指定外键约束名称
    project_id = db.Column(db.Integer, db.ForeignKey('project.id', name='fk_internal_control_test_project_id'), nullable=False)
    test_type = db.Column(db.String(64))  # 例如：符合性测试、实质性测试
    description = db.Column(db.Text, nullable=False)
    result = db.Column(db.Text)
    status = db.Column(db.String(32), default='Pending')
    created_at = db.Column(db.DateTime, default=datetime.utcnow)

class Project(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(128), nullable=False)
    description = db.Column(db.Text)
    start_date = db.Column(db.Date)
    end_date = db.Column(db.Date)
    status = db.Column(db.String(32), default='Pending')
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    # 建立反向关系，方便查询项目关联的文档和测试
    # 修改反向关系定义，添加级联删除
    normative_documents = db.relationship('NormativeDocument', backref='project', lazy=True, cascade="all, delete-orphan")
    project_documents = db.relationship('ProjectDocument', backref='project', lazy=True, cascade="all, delete-orphan")
    internal_control_tests = db.relationship('InternalControlTest', backref='project', lazy=True, cascade="all, delete-orphan")

# DeepSeek_V3辅助函数
def generate_internal_control_test(content):
    client = OpenAI(api_key=DEEPSEEK_API_KEY, base_url="https://api.deepseek.com/v1")
    try:
        response = client.chat.completions.create(
        model="deepseek-chat",
        messages=[
                {
                    "role": "system", 
                    "content": """你是一位资深审计专家，请生成包含以下结构的详细内部控制测评报告：
                        1. 审计概况（包含审计目标、范围、依据）
                        2. 控制环境分析（至少3个维度）
                        3. 风险评估（识别3-5个主要风险点）
                        4. 控制活动评价（按业务环节详细分析）
                        5. 信息系统评估
                        6. 发现的问题（按严重程度分级）
                        7. 改进建议（具体可操作性建议）
                        8. 结论与后续计划

                    要求：
                        - 每个主要部分不少于200字
                        - 包含具体案例数据
                        - 使用表格对比理想控制与实际状况"""
                },
                {
                    "role": "user", 
                    "content": f"""根据以下审计资料生成详细测评报告：
                        # 规范性文件
                        {content.get('normative', '')}
                        # 项目资料
                        {content.get('documents', '')}
                        # 测试结果
                        {content.get('tests', '')}

                    # 补充要求
                    1. 重点分析资金管理和合同审批环节
                    2. 对比行业最佳实践
                    3. 包含至少3个具体改进案例"""
                }
            ],
    temperature=0.3,
    max_tokens=3000,
    stream=False
)
    # 修改为直接访问对象属性
        return response.choices[0].message.content
    except Exception as e:
        # 扩展异常处理范围
        return f"生成报告时发生错误: {str(e)}"

# 添加自定义模板过滤器
@app.template_filter('local_time')
def format_local_time(utc_time):
    if not utc_time:
        return ""
    return utc_time.replace(tzinfo=pytz.utc).astimezone(app.config['TIMEZONE']).strftime('%Y-%m-%d %H:%M:%S')

# 路由：首页
@app.route('/')
def index():
    projects = Project.query.all()
    return render_template('index.html', projects=projects)

# 路由：创建项目表单
@app.route('/projects/create-form')
def create_project_form():
    return render_template('projects/create.html')

# 路由：创建项目
@app.route('/projects/create', methods=['POST'])
def create_project():
    name = request.form.get('name', '').strip()
    description = request.form.get('description', '').strip()
    start_date_str = request.form.get('start_date')
    end_date_str = request.form.get('end_date')

    # 表单验证
    if not name:
        return render_template('projects/create.html', error='项目名称不能为空')
    if not start_date_str or not end_date_str:
        return render_template('projects/create.html', error='开始日期和结束日期不能为空')

    try:
        start_date = datetime.strptime(start_date_str, '%Y-%m-%d').date()
        end_date = datetime.strptime(end_date_str, '%Y-%m-%d').date()
    except ValueError:
        return render_template('projects/create.html', error='日期格式不正确，请使用 YYYY-MM-DD')

    project = Project(
        name=name,
        description=description,
        start_date=start_date,
        end_date=end_date,
        status='Active'
    )
    db.session.add(project)
    db.session.commit()

    return redirect(url_for('index'))

# 路由：上传规范性文件表单
@app.route('/normative-documents/upload-form')
# 新增：显示上传表单的路由（GET请求）
@app.route('/upload_normative_document/<int:project_id>/form', methods=['GET'])
def upload_normative_document_form(project_id):
    # 这里可以根据 project_id 做一些处理，比如查询项目信息
    # 例如：project = Project.query.get(project_id)
    return render_template('normative_documents/upload.html', project_id=project_id)

# 修改路由定义，添加 GET 方法支持
@app.route('/normative-documents/upload', methods=['GET', 'POST'])
def upload_normative_document():
    if request.method == 'GET':
        # 处理 GET 请求，例如显示上传表单
        return render_template('normative_documents/upload.html')
    elif request.method == 'POST':
        # 处理 POST 请求，例如处理文件上传
        project_id = request.form.get('project_id')
        name = request.form.get('name', '').strip()
        description = request.form.get('description', '').strip()
        category = request.form.get('category', '').strip()
        version = request.form.get('version', '').strip()
        file = request.files.get('file')

        # 表单验证
        if not name:
            return render_template('normative_documents/upload.html', error='文件名称不能为空')
        if not file:
            return render_template('normative_documents/upload.html', error='请上传文件')

        # 确保目录存在
        upload_dir = os.path.join(app.config['UPLOAD_FOLDER'], 'normative')
        os.makedirs(upload_dir, exist_ok=True)

        # 使用 secure_filename 处理文件名
        filename = secure_filename(file.filename)
        folder = os.path.join(app.config['UPLOAD_FOLDER'], 'normative')
        file_path = os.path.join(folder, file.filename)
        file.save(file_path)

        document = NormativeDocument(
            project_id=project_id,
            name=name,
            description=description,
            file_path=file_path,
            category=category,
            version=version
        )
        db.session.add(document)
        db.session.commit()

        return redirect(url_for('list_normative_documents', project_id=project_id))

# 路由：列出规范性文件
@app.route('/normative_documents/list/<int:project_id>')
def list_normative_documents(project_id):
    # 直接使用当前文件中定义的 NormativeDocument 模型
    documents = NormativeDocument.query.filter_by(project_id=project_id).all()
    return render_template('normative_documents/list.html', documents=documents, project_id=project_id)

# 路由：上传项目资料表单
@app.route('/projects/<int:project_id>/documents/upload-form')
def upload_project_document_form(project_id):
    return render_template('project_documents/upload.html', project_id=project_id)

# 路由：上传项目资料（修改文件保存逻辑）
@app.route('/projects/<int:project_id>/documents/upload', methods=['POST'])
def upload_project_document(project_id):
    # 添加项目存在性校验
    project = Project.query.get_or_404(project_id)
    
    name = request.form.get('name', '').strip()
    description = request.form.get('description', '').strip()
    document_type = request.form.get('document_type', '').strip()
    file = request.files.get('file')

    # 表单验证
    if not name:
        return render_template('project_documents/upload.html', project_id=project_id, error='文件名称不能为空')
    if not file:
        return render_template('project_documents/upload.html', project_id=project_id, error='请上传文件')

    # +++ 新增扩展名验证 +++
    file_ext = os.path.splitext(file.filename)[1].lower()
    if file_ext not in ALLOWED_EXTENSIONS:
        allowed_exts = ', '.join(ALLOWED_EXTENSIONS)
        return render_template('project_documents/upload.html', 
                            project_id=project_id, 
                            error=f'不支持的文件格式 {file_ext}，请上传 {allowed_exts} 格式文件')

    # 确保目录存在
    upload_dir = os.path.join(app.config['UPLOAD_FOLDER'], 'projects', str(project_id))
    os.makedirs(upload_dir, exist_ok=True)

    # 使用 secure_filename 处理文件名
    filename = secure_filename(file.filename)
    # 原代码：file_path = f"uploads/projects/{project_id}/{file.filename}"
    # 修改为：
    # 修改文件保存路径生成方式
    folder = os.path.join(app.config['UPLOAD_FOLDER'], 'projects', str(project_id))  # 确保转为字符串
    os.makedirs(folder, exist_ok=True)
    file_path = os.path.join(folder, secure_filename(file.filename))  # 使用安全文件名
    
    file.save(file_path)

    document = ProjectDocument(
        project_id=project_id,
        name=name,
        description=description,
        document_type=document_type,
        file_path=file_path
    )
    db.session.add(document)
    db.session.commit()

    return redirect(url_for('project_documents', project_id=project_id))

# 路由：列出项目资料
@app.route('/projects/<int:project_id>/documents')
def project_documents(project_id):
    documents = ProjectDocument.query.filter_by(project_id=project_id).all()
    return render_template('project_documents/list.html', documents=documents, project_id=project_id)

# 路由：创建内部控制测试表单
@app.route('/projects/<int:project_id>/internal-controls/create-form')
def create_internal_control_test_form(project_id):
    return render_template('internal_controls/create.html', project_id=project_id)

# 路由：创建内部控制测试
@app.route('/projects/<int:project_id>/internal-controls/create', methods=['POST'])
def create_internal_control_test(project_id):
    test_type = request.form.get('test_type', '').strip()
    description = request.form.get('description', '').strip()
    result = request.form.get('result', '').strip()

    # 表单验证
    if not test_type:
        return render_template('internal_controls/create.html', project_id=project_id, error='测试类型不能为空')
    if not description:
        return render_template('internal_controls/create.html', project_id=project_id, error='描述不能为空')

    test = InternalControlTest(
        project_id=project_id,
        test_type=test_type,
        description=description,
        result=result,
        status='Completed'
    )
    db.session.add(test)
    db.session.commit()

    return redirect(url_for('project_internal_controls', project_id=project_id))

# 路由：列出内部控制测试
@app.route('/projects/<int:project_id>/internal-controls')
def project_internal_controls(project_id):
    tests = InternalControlTest.query.filter_by(project_id=project_id).all()
    return render_template('internal_controls/list.html', tests=tests, project_id=project_id)

# 路由：生成内部控制测评报告
@app.route('/projects/<int:project_id>/internal-controls/generate-report', methods=['POST'])
def generate_internal_control_report(project_id):
    # 获取项目相关资料
    documents = ProjectDocument.query.filter_by(project_id=project_id).all()
    # 测试列表
    tests = InternalControlTest.query.filter_by(project_id=project_id).all()

    # 新增：获取规范性文件 ▼▼▼▼
    normative_docs = NormativeDocument.query.filter_by(project_id=project_id).all()

    # 整理资料内容
    # document_content = "\n".join([f"{doc.name}: {doc.description}" for doc in documents])
    # test_content = "\n".join([f"{test.test_type}: {test.description} - {test.result}" for test in tests])

    # 生成测评报告
    content = {
    'documents': "\n".join([f"### {doc.name}\n{doc.description}" for doc in documents]),
    'tests': "\n".join([f"#### {test.test_type}\n结果：{test.result}\n详情：{test.description}" for test in tests]),
    'normative': "\n".join([f"### 📜 {doc.name}\n版本：{doc.version}\n{doc.description}" for doc in normative_docs])
    }
    report = generate_internal_control_test(content)
    
    # 新报告内容 加 生成逻辑图▼▼▼▼▼▼▼▼▼▼▼
    kb = EnhancedKnowledgeBase(project_id)
    
    document_paths = [doc.file_path for doc in documents]
    for path in document_paths:
        # raw_text = kb._parse_pdf(path)
        raw_text = kb.parse_file(path)
        text_splitter = RecursiveCharacterTextSplitter(
                chunk_size=2500,
                chunk_overlap=400,
                separators=["\n\n第[一二三四五六七八九十]+章", "\n•\t"]
            )
        split_documents = text_splitter.create_documents([raw_text])
        # 将每个文本块的内容传递给知识库
        for i, doc_chunk in enumerate(split_documents):
            logging.info(f"正在处理文档块 {i+1}/{len(split_documents)} (来源: {doc_chunk.metadata.get('source', '未知')}, 页码: {doc_chunk.metadata.get('page', '未知')})...")
            kb.add_policy_document(doc_chunk.page_content)

    # 新增：从文件中提取业务流程步骤 ▼▼▼▼
    def extract_workflow_steps():
        import re
        steps = []
        patterns = [
            # 增强模式匹配中文数字和不同分隔符
            r'(?:步骤|环节|阶段)?[\(（]?([0-9一二三四五六七八九十]+)[\)）]?[\.、:：]\s*([\u4e00-\u9fa5]+)',
            r'步骤\d+[:：]\s*([\u4e00-\u9fa5]+)',
            # 新增带括号的流程节点模式
            r'流程节点[\(（]?([\u4e00-\u9fa5]+)[\)）]?'
        ]
    
        # 新增：使用EnhancedKnowledgeBase实例进行标准化 ▼▼▼▼
        kb = EnhancedKnowledgeBase(project_id)
    
        for norm_doc in normative_docs:
            try:
                # raw_text = kb._parse_pdf(norm_doc.file_path)
                raw_text = kb.parse_file(norm_doc.file_path)
                # 统一使用知识库的标准化方法 ▼▼▼▼
                for line in raw_text.split('\n'):
                    normalized_line = kb.normalize_step(line.strip())
                    if len(normalized_line) > 2:  # 过滤过短的无效内容
                        steps.append(normalized_line)
            except Exception as e:
                logging.error(f"解析规范性文件失败: {norm_doc.name} - {str(e)}")
        
        # 原有匹配模式保持不变 ▼▼▼▼
        for pattern in patterns:
            for norm_doc in normative_docs:
                try:
                    with open(norm_doc.file_path, 'r', encoding='utf-8') as f:
                        content = f.read()
                        # 扩展多模式匹配逻辑 ▼▼▼▼
                        matches = re.findall(pattern, content)
                        for match in matches:
                            if isinstance(match, tuple):
                                step = match[1] if len(match) > 1 else match[0]
                            else:
                                step = match
                            if step:
                                steps.append(step)
                        # 新增匹配变体处理 ▼▼▼▼
                        # 处理带圈数字的变体（如①、②）
                        circled_num_matches = re.findall(r'[①-⑩]+\s*([\u4e00-\u9fa5]+)', content)
                        steps.extend(circled_num_matches)
                        
                        # 匹配字母编号模式（如A.、B.）
                        alpha_matches = re.findall(r'[A-Za-z][\.、]\s*([\u4e00-\u9fa5]+)', content)
                        steps.extend(alpha_matches)
                        
                        # 匹配无前缀纯数字编号（如1.XXX 2.XXX）
                        pure_num_matches = re.findall(r'\n\d+[\.、:：]\s*([\u4e00-\u9fa5]+)', content)
                        steps.extend(pure_num_matches)
        
                        # 将新增的高级匹配模式移到此处 ▼▼▼▼
                        # 匹配阶段式描述（如第一阶段：XXX）
                        phase_matches = re.findall(r'第[一二三四五六七八九十]+阶段[：:]\s*([\u4e00-\u9fa5]+)', content)
                        steps.extend(phase_matches)
        
                        # 匹配表格中的步骤描述（包含竖线分隔）
                        table_matches = re.findall(r'\|\s*\d+\s*\|\s*([\u4e00-\u9fa5]+)\s*\|', content)
                        steps.extend(table_matches)
        
                        # 匹配流程图中的菱形框文字
                        flowchart_matches = re.findall(r'◇\s*([\u4e00-\u9fa5]+)', content)
                        steps.extend(flowchart_matches)
                except Exception as e:
                    logging.error(f"处理文档时发生错误: {norm_doc.name} - {str(e)}")
        
        # 统一标准化处理 ▼▼▼▼
        steps = [kb.normalize_step(step) for step in steps]
        
        # 增强去重逻辑（保持顺序）
        seen = set()
        unique_steps = []
        for step in steps:
            if step and step not in seen:
                seen.add(step)
                unique_steps.append(step)
        
        return unique_steps
    
    # 生成实际业务流程步骤
    workflow_steps = extract_workflow_steps()

    # 如果自动提取失败则使用默认步骤
    if not workflow_steps:
        logging.warning("未能自动提取流程步骤，使用默认步骤")
        workflow_steps = [
            "合同签署",
            "供应商资质审核",
            "发票验证",
            "付款操作",
            "银行转账完成"
        ]
    # ▲▲▲▲▲

    violations, validation_report = kb.validate_workflow(workflow_steps)
    # report = kb.generate_audit_report(violations, validation_report)
    
    # ▼▼▼▼▼▼▼▼▼▼ 强制图表生成逻辑 ▼▼▼▼▼▼▼▼▼▼
    graph_web_path = None
    MAX_RETRY = 3  # 新增重试机制
    
    for attempt in range(MAX_RETRY):
        try:
            graph_path = kb.visualize_graph()
            # print(graph_path)
            if graph_path and os.path.exists(graph_path):
                # 移动文件到web目录
                target_dir = app.config['GRAPH_UPLOAD_FOLDER']
                os.makedirs(target_dir, exist_ok=True)
                target_path = os.path.join(target_dir, os.path.basename(graph_path))
                shutil.move(graph_path, target_path)
                graph_web_path = url_for('serve_graph', filename=os.path.basename(target_path))
                break
        except Exception as e:
            logging.error(f"图表生成异常（第{attempt+1}次重试）: {str(e)}")
            if attempt == MAX_RETRY - 1:
                logging.error("图表生成最终失败，使用备用图")
                graph_web_path = url_for('serve_graph', filename='fallback_graph.png')

    # 终极保障：如果仍然失败则使用预存图片
    if not graph_web_path:
        graph_web_path = url_for('static', filename='images/fallback_graph.png')
    # ▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲
    
    return render_template('internal_controls/report.html', report=report, graph_url=graph_web_path, project_id=project_id)

@app.route('/delete_project/<int:project_id>')
def delete_project(project_id):
    project = Project.query.get_or_404(project_id)
    
    try:
        # 删除项目关联的规范性文件
        for norm_doc in project.normative_documents:
            # 删除物理文件
            if os.path.exists(norm_doc.file_path):
                os.remove(norm_doc.file_path)
                
            # 删除规范性文件项目专属目录（如果存在）
            normative_project_dir = os.path.join(
                app.config['UPLOAD_FOLDER'],
                'normative',
                f'project_{project_id}'  #假设目录命名规则为project_+项目ID
            )
            if os.path.exists(normative_project_dir):
                shutil.rmtree(normative_project_dir)
        
        # 删除项目文档专属目录（保留原有逻辑）
        project_dir = os.path.join(
            app.config['UPLOAD_FOLDER'],
            'projects',
            str(project_id)
        )
        if os.path.exists(project_dir):
            shutil.rmtree(project_dir)

        # ========== 新增图表清理代码 ==========
        graph_project_dir = os.path.join(app.config['GRAPH_UPLOAD_FOLDER'], f'project_{project_id}')
        if os.path.exists(graph_project_dir):
            shutil.rmtree(graph_project_dir)
            
        graph_files = glob.glob(os.path.join(
            app.config['GRAPH_UPLOAD_FOLDER'], 
            'workflow_graph_*.png'  # 匹配所有工作流图表文件
        ))
        for file in graph_files:
            if os.path.exists(file):
                os.remove(file)
        # ========== 新增代码结束 ==========
            
    except Exception as e:
        app.logger.error(f"文件删除失败: {str(e)}")

    # 删除数据库记录（自动级联删除关联记录）
    db.session.delete(project)
    db.session.commit()
    
    return redirect(url_for('index'))

@app.route('/download/<path:file_path>')
def download_file(file_path):
    try:
        return send_file(file_path, as_attachment=True)
    except Exception as e:
        return str(e), 404

# 新增图形文件访问路由 ▼▼▼▼
@app.route('/uploads/graphs/<filename>')
def serve_graph(filename):
    return send_from_directory(app.config['GRAPH_UPLOAD_FOLDER'], filename)

if __name__ == '__main__':
    # 创建上传目录（在应用启动时）
    for folder in ['normative', 'projects']:
        path = os.path.join(app.config['UPLOAD_FOLDER'], folder)
        os.makedirs(path, exist_ok=True)  # exist_ok=True 防止目录已存在时报错
    
    with app.app_context():
        db.create_all()
    app.run(debug=True)